ШАГ 5 - Функции первой группы
Эти упражнения охватывают использование функций не только во фразе SELЕСТ, но и во фразах WHERE и ORDER. BY. Если результат содержит псевдонимы -- используйте их во фразе SELЕСТ команды SQL.
Функции в Oracle делятся на 2 группы.
Первая:
- символьные;
- числовые;
Вторая:
- над датами:
- конвертирования типов данных;
- обрабатывающие все типы данных на входе;
Символьные функции
LOWER(столбец|значение)
-- перевод на нижний регистр;
UPPER(столбец|значение)-- перевод на верхний регистр;
INITCAP(столбец|значение)-- первая буква прописная, остальные строчные;
CONCAT(строка1, строка2)-- конкатенация строк (альтернатива ||);
LPAD(столбец|литерал, n,»строка»)-- используя «строку» в качестве заполнителя заполняет столбец или литерал слева до n символов;
RPAD(столбец|литерал, n,»строка»)-- то же, но заполняет справа;
SUBSTR(столбец|литерал,номер,n)-- возвращает фрагмент столбца или литерала длиной в n символов, начиная с позиции номер; если число n не задано, возвращается фрагмент с позиции номер и до конца;
INSTR(столбец|литерал, «строка», [номер, n])-- определяет начальную позицию в кратком варианте первого, в длинном n-го вхождения строки в значение столбца или литерала;
LTRIM(столбец|литерал,«символ(ы)»),
RTRIM(столбец|литерал,«символ(ы)»)-- удаляет слева (справа) все головные(хвостовые) символы, перечисленные в наборе -символ(ы); если последний аргумент не задан удаляются все головные (хвостовые) пробелы;
SOUNDEX(столбец|литерал)-- только для английского языка; возвращает фонетическое представление слов(а) в значении столбца или литерала;
LENGTH(столбец|литерал)-- возвращает число символов в значении;
TRANSLATE(столбец|литерал,символ(ы)1,символ(ы)2)-- заменяет символы из набора символ(ы)1 на соответствующие символы из набора символ(ы)2 ; если соответствующий символ в наборе символ(ы)2 не задан, то символ из символ(ы)1 удаляется;
REPLACE(столбец|литерал, строка_образец,строка_замены) -- преобразует значение столбца или литерала, заменяя в нем строку_образец при каждом ее вхождении на строку_замены; Если строка_замены не задана то при каждом вхождении удаляется строка_образец;
Числовые функции
ROUND(столбец|значение, n)-- округляет столбец, выражение или значение до n десятичных знаков; если же n не указано, округление до целых чисел; при n<0 округление до n-го знака слева от десятичной точки;
TRUNC(столбец|значение, n)-- усечение до n десятичных знаков; при отсутствии n дробная часть отбрасывается. при n<0 n знаков слева от десятичной точки заменяются на 0;
CEIL(столбец|значение)-- наименьшее целое превышающее аргумент или равное ему;
FLOOR(столбец|значение)-- наибольшее целое меньшее аргумента или равное ему;
POWER(столбец|значение, n)-- возведение в степень n;
EXP(n)-- экспоненциал;
SQRT(столбец|значение)-- корень квадратный; если аргумент <0 или NULL, возвращается NULL;
SIGN(столбец|значение)-- знаковая функция (-1,0+1); часто используют для сравнения значений;
ABS(столбец|значение)-- модуль;
MOD(значение1, значение2)-- остаток от деления значения1 на значение2;
LOG(m,n)-- логарифм от n по основанию m;
SIN(n), SINH(n), TAN(n), TANH(n), COS(n), COSH(n);
Для строчных функций допускается вычисление функции от функции. Глубина вложенности любая. Вычисление вложенных функций начинается с самого нижнего уровня. Например, для определения количества букв ‘S’ в значении столбца можно использовать один из запросов:
SELECT DNAME, LENGTH(DNAME), LENGTH(DNAME)- LENGTH(TRANSLATE(DNAME, 'AS','A')) FROM DEPT;
или
SELECT DNAME, LENGTH(DNAME), LENGTH(DNAME) - LENGTH(REPLACE(DNAME, 'S')) FROM DEPT;
Упражнения
1. Перечислим имена сотрудников и их оклад, увеличенный на 15% и округленный до целого числа долларов.
SELЕСТ DEPTNO, ЕNАМЕ, ROUND (SAL* 1.15) PCTSAL FRОМ EMP;
В окне SQL*Plus Вы должны получить следующие значения:
DEPTNO ENAME ROUND(SAL*1.15)
------ ----- --------------
20 SMITH 920
30 ALLEN 1840
30 WARD 1438
20 JONES 3421
30 MARTIN 1438
30 BLAKE 3278
.. .... ....
2. Выведем фамилии сотрудников с их должностью в скобках рядом. Должность должна начинаться с заглавной буквы. Все должно находиться в одном столбце.
SELЕСТ ENAME || ’(‘ || initcap(job) || ')' EMPLOYEE FRОМ EMP;
В окне SQL*Plus Вы должны получить следующие значения:
EMPLOYEE
---------------
SMITH(Clerk)
ALLEN(Salesman)
WARD(Salesman)
JONES(Manager)
MARTIN(Salesman)
BLAKE(Manager)
...
3. Напишем команду SELECT для отбора сотрудников по должности, которая будет задаваться вами во время выполнения. Сделаем это так, чтобы должность можно было вводить как строчными, так и прописными буквами.
SELЕСТ * FRОМ EMP WHERE UPPER (JOB) = UPPER('&JOB');
После ввода должности MANAGER В окне SQL*Plus Вы должны получить следующие значения:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
4. Известно, что в отделе номер 30 некоторые из продавцов -- женщины (поэтому для них название SALESMAN не уместно). Вот что мы можем получить запросом ниже.
SELЕСТ ENAME, DEPTNO, INITCAP(REPLACE(JOB,'SALESMAN', 'SALESPERSON')) JOB FRОМ EMP WHERE DEPTNO = 30;
В окне SQL*Plus Вы должны получить следующие значения:
ENAME DEPTNO JOB
---------- ---------- -------------
ALLEN 30 Salesperson
WARD 30 Salesperson
MARTIN 30 Salesperson
BLAKE 30 Manager
TURNER 30 Salesperson
JAMES 30 Clerk
Автор: Michael Nemtsev aka 'LaFlour'
